package com.jd.risk.bankorder.util.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.wangyin.operation.beans.UploadFile;
import com.wangyin.operation.beans.UploadObject;

public class FileExcelUtil {
	private final static Logger logger = LoggerFactory.getLogger(FileExcelUtil.class);
	
	public static  List<Map<String,String>> readExcelToBean(Object uploadInfo,int startRows,Map<Integer ,String> map){
		String uploadObjectStr = String.valueOf(uploadInfo);
		JSONObject obj = JSONObject.parseObject(uploadObjectStr);
		UploadObject uploadObject = JSON.toJavaObject(obj, UploadObject.class);
		List<UploadFile> list = uploadObject.getFileList();//得到上传文件列表
		if(uploadObject.isSuccess()){//判断整个批次是否上传成功
			for (UploadFile uploadFile : list) {
				InputStream is = null;
				try {
					is = new FileInputStream(uploadFile.getRealPathname());
					return readExcel(is,uploadFile.getName(),startRows ,map);
				} catch (FileNotFoundException e1) {
					logger.info("文件没有找到",e1);
				} catch (IOException e) {
					logger.info("读取文件失败",e);
				}
			}
		}
		return null;
	}

	public static  List<Map<String,String>> readExcel(InputStream is,String fileName,int startRows,Map<Integer ,String> map) throws IOException {
		Workbook wb;
		String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);
		if("xls".equals(extension)){
			wb = new HSSFWorkbook(is);
			is.close();
		}else if("xlsx".equals(extension)){
			wb = new XSSFWorkbook(is);
			is.close();
		}else{
			throw new IOException("不支持的文件类型");
		}
		return readAllExcel(wb,startRows , map);
	}
	
	private static List<Map<String,String>> readAllExcel(Workbook wb,int startRows , Map<Integer ,String> map) throws IOException {
		List<Map<String,String>>  list = new ArrayList<Map<String, String>>();
	    Sheet sheet = wb.getSheetAt(0);
	    int rows = sheet.getLastRowNum() - sheet.getFirstRowNum();//总行数
	    for(int r=startRows; r<=rows; r++){
	    	Row row = sheet.getRow(r);
	    	if (row == null) {
	    		continue;  
    	    }
	    	int cols = row.getLastCellNum() - row.getFirstCellNum();//总列数
			try {
				Map<String,String> item =  new HashMap<String, String>();
		    	for (int c=0; c<cols; c++) {
		    		Cell cell = row.getCell(c);
					String filed = map.get(c);
					String val = getCellValue(cell);
					item.put(filed ,val);
		         }
		    	list.add(item);
			} catch (Exception e) {
				logger.info("解析excel文件失败",e);
			}
	    }
		return list;
	}
	
	public static String getCellStringValue(Cell cell){
		if(cell!=null){
			cell.setCellType (Cell.CELL_TYPE_STRING);//设置类型为字符串文本
			String cellValue =cell.toString();
			return cellValue;
		}
		return "";
	}

	public  static String getCellValue(Cell cell){
		String cellValue=null;
		if(cell!=null){
						cell.setCellType (Cell.CELL_TYPE_STRING);//设置类型为字符串文本
						String cellStr =cell.toString();
						cellValue = cellStr.replaceAll("\t|\r|\n","");
			}
		return cellValue;
	}
	
	public static Date getExcelDate(Cell cell){
		if (DateUtil.isCellDateFormatted(cell)) {
            Date theDate = cell.getDateCellValue();  
            return theDate;
        }
		return null;
	}
}
